<?php

function tutup_bulan(){


	$periode_id =get_periode_gaji();
	$query = "UPDATE pr_gaji SET 
		periode_id = ".$periode_id." 
		WHERE periode_id=0";
	$result = my_query($query);		
	$query = "UPDATE pr_gaji_komponen SET 
		periode_id = ".$periode_id." 
		WHERE periode_id=0";
	$result = my_query($query);		
	$query = "UPDATE pr_gaji_pajak SET 
		periode_id = ".$periode_id." 
		WHERE periode_id=0";
	$result = my_query($query);	 
	
	//$query = "DELETE FROM pr_penyesuaian WHERE reset='Y'";
	//$result = my_query($query);	
	 
	//$query = "TRUNCATE TABLE pr_gaji_upload_view";
	//$result = my_query($query);		

	stor_time_management_close($periode_id);
	update_periode_id();
	pr_upload_copy($periode_id);
}


function pr_upload_copy($last_periode_id){
	$query = "SELECT komponen_gaji_id , tipe FROM pr_komponen_gaji WHERE model_entry = '1' AND reset_tutup_bulan <> 'yes' ";
	$result = my_query($query);
	while($row = my_fetch_array($result)){ 
		if($row['tipe'] == '2')
		$query = "
		INSERT INTO pr_gaji_upload_view ( komponen_gaji_id ,karyawan_id	,nominal_bl,nominal)
			SELECT komponen_id,	karyawan_id	, NULL,   (-1) * kalkulasi 	  FROM pr_gaji_komponen 
			WHERE komponen_id ={$row['komponen_gaji_id']} AND periode_id = {$last_periode_id}
		"; 
		else
		$query = "
		INSERT INTO pr_gaji_upload_view ( komponen_gaji_id ,karyawan_id	,nominal_bl,nominal)
			SELECT komponen_id,	karyawan_id	, NULL,  kalkulasi 	  FROM pr_gaji_komponen 
			WHERE komponen_id ={$row['komponen_gaji_id']} AND periode_id = {$last_periode_id}
		"; 
		 return my_query($query);
	}
}

function update_periode_id(){
	$query = "
		SELECT periode_id,tanggal,tahun FROM pr_periode 
		WHERE gaji = 'Active'";
	$res = my_query($query);
	$rw = my_fetch_array($res);
	$periode_id=$rw['periode_id'];
	$bulan = (int) date('m' , strtotime($rw['tanggal']));
	$tahun = $rw['tahun'] ;
	//Reset Active->Void
	$datas = array(
		'gaji'=>my_type_data_str('Void'),	
	);
	my_update_record('pr_periode','periode_id',$periode_id,$datas);		
	
	//cek apakah periode id berikutnya sudah ada
	
	$next_periode=get_periode_berikut($periode_id);
	 
	$datas = array(
		'gaji'=>my_type_data_str('Active'),	
	);
	return my_update_record('pr_periode','periode_id',$next_periode,$datas);		
	  
}

function update_periode_id_old(){
	$query = "
		SELECT periode_id,tanggal,tahun FROM pr_periode 
		WHERE gaji = 'Active'";
	$res = my_query($query);
	$rw = my_fetch_array($res);
	$periode_id=$rw['periode_id'];
	$bulan = (int) date('m' , strtotime($rw['tanggal']));
	$tahun = $rw['tahun'] ;
	//Reset Active->Void
	$datas = array(
		'gaji'=>my_type_data_str('Void'),	
	);
	my_update_record('pr_periode','periode_id',$periode_id,$datas);		
	
	//cek apakah periode id berikutnya sudah ada
	
	$next_periode=get_periode_berikut($periode_id);
	if($next_periode==0){
		$tgl_dari = $tahun.'-'. $bulan.'-11';
		if($bulan ==12 ){
			$tahun++ ;
			$tanggal = $tahun.'-01-01';
			$tgl_sampai=$tahun.'-01-10';
		}else{
			$tanggal = $tahun.'-'.sprintf( '%02d',$bulan+1) .'-01';
			$tgl_sampai=$tahun.'-'.sprintf( '%02d',$bulan+1) .'-10';
		}
		$datas = array(
			'tahun'=>my_type_data_str($tahun),
			'tanggal'=>my_type_data_str($tanggal),
			'absen'=>my_type_data_str('Void'),
			'gaji'=>my_type_data_str('Active'),
			'dari'=>my_type_data_str($tgl_dari),
			'sampai'=>my_type_data_str($tgl_sampai),
			);
		my_insert_record('pr_periode' , $datas);
	}else{
		$periode_id=$next_periode;
		$datas = array(
			'gaji'=>my_type_data_str('Active'),	
		);
		my_update_record('pr_periode','periode_id',$next_periode,$datas);		
	}
	return true;
}

function stor_time_management_close($periode_id){

	//STOR HARI KERJA
	get_hari_kerja($periode_id);
	my_query('TRUNCATE TABLE temp_tmn_hari');
	
	//STOR REALISASI HARI 
	$query_khk = "INSERT INTO tmn_hari_realisasi ( periode_id,  tgl_index,	hari_libur	,karyawan_id,	status_absen )  
	SELECT '{$periode_id}', tgl_index,	hari_libur	,karyawan_id,	status_absen  FROM temp_tmn_hari ";
	my_query($query_khk);
	my_query('TRUNCATE TABLE temp_tmn_hari');

	//STOR KHK
	$query_khk = "INSERT INTO tmn_khk ( periode_id,	karyawan_id	,nhari) 
	SELECT '{$periode_id}' , karyawan_id , nhari FROM temp_tmn_khk ";
	my_query($query_khk);
	my_query("TRUNCATE TABLE temp_tmn_khk");
	
	//STOR KJK 
	$query_kjk = "INSERT INTO tmn_kjk ( periode_id,	karyawan_id	,njam) 
	SELECT '{$periode_id}' , karyawan_id , njam FROM temp_tmn_kjk "; 
	my_query($query_kjk);
	my_query("TRUNCATE TABLE temp_tmn_kjk");
	
	//STOR ABSENSI
	$query_absensi = " INSERT INTO tmn_absensi (periode_id	,karyawan_id	,nmenit) 
	SELECT '{$periode_id}' , karyawan_id , nmenit FROM temp_tmn_absensi WHERE nmenit > 0 ";
	my_query($query_absensi);
	my_query("TRUNCATE TABLE temp_tmn_absensi");
	
	//STOR HARIKERJA
	$query_realisasi = "
		INSERT INTO tmn_hari_realisasi (periode_id , tgl_index , hari_libur	,karyawan_id ,	status_absen)
		SELECT '{$periode_id}' , tgl_index , hari_libur	,karyawan_id ,	status_absen FROM  temp_tmn_hari
	";
	my_query($query_realisasi);
	my_query("TRUNCATE TABLE temp_tmn_hari");


	//STOR FINGER
		$query_finger =" INSERT INTO tmn_proses_finger( periode_id  ,
			karyawan_id, proses_date,
			jam_in ,	jam_out,	correct_in,	
			correct_out	,margin_in,	margin_out,	margin_calc,	datetime_updated )
		SELECT '{$periode_id}', karyawan_id , proses_date,	jam_in,	jam_out,	correct_in,	correct_out,	margin_in,	
			margin_out,	margin_calc	,datetime_updated 
		FROM temp_proses_finger";

	my_query($query_finger);
	my_query("TRUNCATE TABLE temp_proses_finger");


	//STOR OVERTIME
	$query_overtime = "
	INSERT INTO tmn_overtime ( periode_id,	karyawan_id,	nik,	tanggal,	
		daytype,	groupday,	hour,	hourpay	, out_of_count,	nominal)
	SELECT '{$periode_id}' ,karyawan_id,	nik,	tanggal,	daytype,	
		groupday,	hour,	hourpay	, out_of_count,	nominal 
	FROM temp_tmn_overtime
	";
	my_query($query_overtime);
	my_query("TRUNCATE TABLE temp_tmn_overtime");
	

}

function get_hari_kerja($periode_id){
	$end=get_batas_tmt() ;
	$query = " SELECT a.karyawan_id FROM karyawan a
				INNER JOIN kekaryawanan b ON b.karyawan_id = a.karyawan_id 
				WHERE b.tmb  <= '{$end}' 
				AND a.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable)
				AND a.karyawan_id NOT IN (select karyawan_id FROM pemberhentian  WHERE tmt <= '{$end}') 	
			";
	$result = my_query($query);
	while($ey = my_fetch_array($result)){
		$dfs = array(
			'periode_id'=>my_type_data_int( $periode_id ), 
			'karyawan_id'=>my_type_data_int( $ey['karyawan_id']), 
			'in'=>my_type_data_str(total_masuk($ey['karyawan_id'] )), 
			'pp'=>my_type_data_str(total_masuk($ey['karyawan_id'] , 'PP')),
			'bt'=>my_type_data_str(total_masuk($ey['karyawan_id'] , 'BT')),
			'bl'=>my_type_data_str(total_masuk($ey['karyawan_id'] , 'BL')),
			'bp'=>my_type_data_str(total_masuk($ey['karyawan_id'] , 'BP')),
			'sd'=>my_type_data_str(total_masuk($ey['karyawan_id'] , 'SD')),
			'c'=>my_type_data_str(total_masuk($ey['karyawan_id'] , 'C')), 
			'save_hari_prev_um'=>my_type_data_str(get_hari_prev_um($ey['karyawan_id'] )), 
		);
		my_insert_record('tmn_hari_kerja' , $dfs); 
	}
	return true;	
}

function total_masuk($karyawan_id , $prefix = false){
	$query = "SELECT COUNT(*) AS ttl FROM temp_tmn_hari WHERE karyawan_id = {$karyawan_id} ";
	
	if($prefix){
		$query .= " AND status_absen = '{$prefix}' ";
	}else{
		$query .= " AND 
		( 
			status_absen = 'N' OR
			status_absen = '1' OR
			status_absen = '2' OR
			status_absen = '3' 
		)";
	}
	$result = my_query($query);
	$row = my_fetch_array($result);
	return $row['ttl'];
}

function get_hari_prev_um($karyawan_id  ){
	$query = "SELECT COUNT(*) AS ttl FROM temp_tmn_hari WHERE karyawan_id = {$karyawan_id} ";
	
	 $query .= " AND tgl_index < 11 ";
		$query .= " AND 
		( 
			status_absen = 'N' OR
			status_absen = '1' OR
			status_absen = '2' OR
			status_absen = '3' 
		)";
	 
	$result = my_query($query);
	$row = my_fetch_array($result);
	return $row['ttl'];
}

function get_periode_berikut($periode_id){
	$query = "SELECT periode_id FROM pr_periode WHERE absen ='Active' LIMIT 1"; 
	$result = my_query($query);
	$row = my_fetch_array($result);
	return $row['periode_id'];
}
 
function get_periode_berikut_old($periode_id){
	$periode = my_get_data_by_id('pr_periode','periode_id',(int) $periode_id);
	$month = date('m',strtotime($periode['tanggal']));
	$year = date('Y',strtotime($periode['tanggal']));
	if($month==12){
		$month=1;
		$year++;
	}else{
		$month++;
	}
	$tanggal=$year.'-'.sprintf( '%02d',$month) .'-01';
	$periods = my_get_data_by_id('pr_periode','tanggal',"'".$tanggal."'");
	$res=(int) $periods['periode_id'];
	return $res;
}